﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DBInfo;
using System.Data;

namespace DataHelper
{
    public abstract class SqlDataHelper
    {
        private SqlConnection cnnHelper;
        private string messageHelper;
        private string messageServer;
        private int errorNumber;
        private bool isValidConnection;
        private IDbSettings sqlSettings;

        public SqlDataHelper(DBInfo.IDbSettings sqlSet)
        {
            sqlSettings = sqlSet;
            cnnHelper = new SqlConnection();

            if (IsValidConnection())
            {
                cnnHelper.InfoMessage +=
                    new SqlInfoMessageEventHandler(cnnHelper_InfoMessage);
            }
        }

        private void cnnHelper_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            messageServer = e.Message;
        }
        private bool IsValidConnection()
        {
            errorNumber = 0;
            if (cnnHelper.State == ConnectionState.Open)
                cnnHelper.Close();

            cnnHelper.ConnectionString = sqlSettings.GetConnectionString();
            try
            {
                if (!isValidConnection)
                {
                    cnnHelper.Open();
                    cnnHelper.Close();
                }
                isValidConnection = true;
                messageHelper = "Conexión exitosa";
            }
            catch (SqlException ex)
            {
                isValidConnection = false;
                errorNumber = ex.Number;
                messageHelper = ex.Message;
            }
            return isValidConnection;
        }

        protected int ExecuteTransaction(SqlCommand cmd)
        {
            errorNumber = 0;
            if (!IsValidConnection())
                return -1;

            SqlTransaction trn = null;
            int res;
            cmd.Connection = cnnHelper;
            using (cnnHelper)
            {
                try
                {
                    cnnHelper.Open();
                    trn = cnnHelper.BeginTransaction();
                    cmd.Transaction = trn;
                    res = cmd.ExecuteNonQuery();
                    trn.Commit();
                    if (res > 0)
                        messageHelper =
                            string.Format(
                            "La operación se realizó con éxito.\nSe afectaron {0} filas",
                            res.ToString());
                    else
                        messageHelper = "La operación no afectó ninguna fila";
                }
                catch (SqlException ex)
                {
                    trn.Rollback();
                    errorNumber = ex.Number;
                    messageHelper = ex.Message;
                    res = -1;
                }
            }
            return res;
        }
        protected int ExecuteQuery(SqlCommand cmd)
        {
            errorNumber = 0;
            int res;
            if (!IsValidConnection())
                return -1;

            cmd.Connection = cnnHelper;

            using (cnnHelper)
            {
                try
                {
                    cnnHelper.Open();
                    res = cmd.ExecuteNonQuery();
                    if (res > 0)
                        messageHelper =
                            string.Format(
                            "La operación se realizó con éxito.\nSe afectaron {0} filas",
                            res.ToString());
                    else
                        messageHelper = "La operación no afectó ninguna fila";

                }
                catch (SqlException ex)
                {
                    errorNumber = ex.Number;
                    messageHelper = ex.Message;
                    res = -1;
                }
            }
            return res;
        }
        protected DataSet GetQueries(SqlCommand cmd)
        {
            errorNumber = 0;
            SqlDataAdapter da;
            DataSet ds = new DataSet();

            if (!IsValidConnection())
                return ds;

            cmd.Connection = cnnHelper;
            da = new SqlDataAdapter(cmd);

            try
            {
                da.Fill(ds);
                messageHelper = "La operación resultó un éxito";
            }
            catch (SqlException ex)
            {
                errorNumber = ex.Number;
                messageHelper = ex.Message;
            }
            return ds;
        }
        protected DataTable GetQuery(SqlCommand cmd)
        {
            errorNumber = 0;
            SqlDataAdapter da;
            DataTable dt = new DataTable();

            if (!IsValidConnection())
                return dt;

            cmd.Connection = cnnHelper;
            da = new SqlDataAdapter(cmd);

            try
            {
                da.Fill(dt);
                messageHelper = "La operación resultó un éxito";
            }
            catch (SqlException ex)
            {
                errorNumber = ex.Number;
                messageHelper = ex.Message;
            }
            return dt;
        }
        protected DataRow GetRecord(SqlCommand cmd)
        {
            DataTable dt = GetQuery(cmd);
            if (dt.Rows.Count > 0)
                return dt.Rows[0];
            else
                return null;
        }
        protected object GetScalar(SqlCommand cmd)
        {
            errorNumber = 0;
            if (!IsValidConnection())
                return null;

            cmd.Connection = cnnHelper;

            using (cnnHelper)
            {
                try
                {
                    cnnHelper.Open();
                    messageHelper = "La operación se completó con éxito";
                    return cmd.ExecuteScalar();
                }
                catch (SqlException ex)
                {
                    errorNumber = ex.Number;
                    messageHelper = ex.Message;
                    return null;
                }
                catch (InvalidCastException ex)
                {
                    errorNumber = -2;
                    messageHelper = ex.Message;
                    return null;
                }
            }
        }

        protected SqlTransaction GetCommonTransaction()
        {
            if (!IsValidConnection())
                return null;

            if (cnnHelper.State == ConnectionState.Closed)
                cnnHelper.Open();

            return cnnHelper.BeginTransaction();
        }

        protected int ExecuteTransaction(SqlCommand cmd, SqlTransaction trn)
        {
            errorNumber = 0;
            SqlConnection cnn = trn.Connection;
            if (cnn.State == ConnectionState.Closed)
                return -1;

            int res;
            try
            {
                cmd.Connection = cnn;
                cmd.Transaction = trn;
                res = cmd.ExecuteNonQuery();
                if (res > 0)
                    messageHelper =
                        string.Format(
                        "La operación se realizó con éxito." +
                        "\nSe afectaron {0} filas",
                        res.ToString());
                else
                    messageHelper = "La operación no afectó ninguna fila";
            }
            catch (SqlException ex)
            {
                trn.Rollback();
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
                errorNumber = ex.Number;
                messageHelper = ex.Message;
                res = -1;
            }
            return res;
        }
        protected DataTable GetQuery(SqlCommand cmd, SqlTransaction trn)
        {
            errorNumber = 0;
            SqlDataAdapter da;
            DataTable dt = new DataTable();

            if (trn.Connection == null || trn.Connection.State == ConnectionState.Closed)
                return new DataTable();

            cmd.Connection = trn.Connection;
            cmd.Transaction = trn;

            da = new SqlDataAdapter(cmd);

            try
            {
                da.Fill(dt);
                messageHelper = "La operación resultó un éxito";
            }
            catch (SqlException ex)
            {
                errorNumber = ex.Number;
                messageHelper = ex.Message;
            }
            return dt;
        }
        protected DataRow GetRecord(SqlCommand cmd, SqlTransaction trn)
        {
            DataTable dt = GetQuery(cmd, trn);
            if (dt.Rows.Count > 0)
                return dt.Rows[0];
            else
                return null;
        }
        protected object GetScalar(SqlCommand cmd, SqlTransaction trn)
        {
            errorNumber = 0;
            SqlConnection cnn = trn.Connection;
            try
            {
                cmd.Connection = cnn;
                cmd.Transaction = trn;
                return cmd.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                errorNumber = ex.Number;
                messageHelper = ex.Message;
                return null;
            }
            catch (InvalidCastException ex)
            {
                errorNumber = -2;
                messageHelper = ex.Message;
                return null;
            }
        }


        public IDbSettings SqlSettings
        {
            get
            {
                return sqlSettings;
            }
        }
        public int ErrorNumber
        {
            get
            {
                return errorNumber;
            }
        }
        public string MessageHelper
        {
            get
            {
                return messageHelper;
            }
        }
        public string MessageServer
        {
            get
            {
                return messageServer;
            }
        }
    }
}